/**
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 * @data:2017年9月24日
 * @author:MaShibo
 */
package com.wttech.dzzf.process.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.FundAccount;
import com.wttech.dzzf.process.model.IntKeyValue;

/**
 * 客户信息治理Dao实现
 * 
 * @date 2017年9月24日
 * @author MaShibo
 */
@Repository
public class ProcessUserInfoDao extends BaseDataTemplate {
    
    
    @Autowired
    public ProcessUserInfoDao(JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }
    
    /**
     * 按照证件类型分组查询每种证件类型数量
     * 
     * @return 每种证件类型的数量
     */
    public List<IntKeyValue> queryFundAccountNumGroupbyCertifiType() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT A.CERTIFITYPE AS KEY, COUNT(1) AS VALUE ");
        sql.append("   FROM AC_FUNDACCOUNT A ");
        sql.append("  WHERE A.ACCOUNTSTATUS = 1 ");
        sql.append("  GROUP BY A.CERTIFITYPE ");
        sql.append("  ORDER BY CERTIFITYPE ");
        return jdbcTemplate.query(sql.toString(), BeanPropertyRowMapper.newInstance(IntKeyValue.class));
    }
    
    @SuppressWarnings("unchecked")
    public List<FundAccount> queryFundAccountByConditions(Map<String, Object> conditions) {
        
        final String baseSQL = " SELECT A.* FROM AC_FUNDACCOUNT A WHERE 1 = 1 ";
        
        StringBuilder sql = new StringBuilder(baseSQL);
        List<Object> params = new ArrayList<Object>();
        
        Object certifiType = conditions.get("certifiType");
        if (certifiType != null) {
            sql.append(" AND A.CERTIFITYPE = ? ");
            params.add(certifiType);
        }
        
        return (List<FundAccount>) jdbcTemplate.query(sql.toString(), params.toArray(),
                new ListResultSetExtractor(new FundAccount()));
    }
    
    public int queryFundAccountNumberByConditions(Map<String, Object> conditions) {
        
        final String baseSQL = " SELECT COUNT(1) FROM AC_FUNDACCOUNT A WHERE 1 = 1 ";
        
        StringBuilder sql = new StringBuilder(baseSQL);
        List<Object> params = new ArrayList<Object>();
        
        Object certifiType = conditions.get("certifiType");
        if (certifiType != null) {
            sql.append(" AND A.CERTIFITYPE = ? ");
            params.add(certifiType);
        }
        
        return jdbcTemplate.queryForObject(sql.toString(), params.toArray(), Integer.class);
    }
    
    @SuppressWarnings("unchecked")
    public List<FundAccount> queryFundAccountByPageInfo(Map<String, Object> conditions, int startRow, int endRow) {
        
        final String baseSQL = " SELECT A.*, ROWNUM AS RM FROM AC_FUNDACCOUNT A WHERE 1 = 1 ";
        
        StringBuilder sql = new StringBuilder(baseSQL);
        List<Object> params = new ArrayList<Object>();
        
        Object certifiType = conditions.get("certifiType");
        if (certifiType != null) {
            sql.append(" AND A.CERTIFITYPE = ? ");
            params.add(certifiType);
        }
        
        sql = new StringBuilder(" SELECT * FROM ( ").append(sql.toString()).append(") ")
                .append(" WHERE RM >= ? AND RM <= ? ");
        params.add(startRow);
        params.add(endRow);
        
        return (List<FundAccount>) jdbcTemplate.query(sql.toString(), params.toArray(),
                new ListResultSetExtractor(new FundAccount()));
    }
    
    /**
     * 批量插入错误客户信息
     * 
     * @param errorInfos
     *            错误客户信息
     * @param errorType
     *            错误类型
     * @return 更新数量
     */
    public int[] batchIntertErrorInfo(final List<FundAccount> errorInfos, final int errorType) {
        final String sql = "INSERT INTO AC_ERRORINFO (FUNDACCOUNTID, ERRORTYPE, CERTIFITYPE, CERTIFINO, OWNERTYPE, STATE) VALUES (?, ?, ?, ?, ?, 0) ";
        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            
            
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, errorInfos.get(i).getFundAccountId());
                ps.setInt(2, errorType);
                ps.setInt(3, errorInfos.get(i).getCertifiType());
                ps.setString(4, errorInfos.get(i).getCompanyLic());
                ps.setInt(5, errorInfos.get(i).getOwnerType());
            }
            
            @Override
            public int getBatchSize() {
                return errorInfos.size();
            }
        });
    }
    
    /**
     * 删除错误信息表
     */
    public void trucateErrorTable() {
        final String sql = "TRUNCATE TABLE DZZFUSER.AC_ERRORINFO";
        jdbcTemplate.execute(sql);
    }
    
}
